<?php

namespace MLM\Model\Network;


use MLM\Model\Network;

class Storage extends \MLM\Model\Generic\Db\Storage {

    
    public function getMemberDetail($key){
        
        $sql = "select x.*, sp.member_code as sponsor_code, sp.member_name as sponsor_name from (
                select m.member_id, m.member_code, m.member_name, m.pin, a.contact_name, a.contact_email, a.contact_mobile,
                a.contact_phone, a.contact_address, a.contact_city, a.contact_province, a.country_id, a.post_code,
                account_name, bank_acc_no, bank_city_name, bank_acc_name, member_type_name,
                m.sponsor_id,
                m.matrik1_id
                from members m, member_contact c, contact a, accounts ac, member_type mt
                where m.member_id = ?
                and c.member_id = m.member_id 
                and a.contact_id = c.contact_id
                and ac.member_id = m.member_id and mt.member_type_id = m.member_type_id
                ) x left outer join members sp on sp.member_id = x.sponsor_id";

        //$sql = "select * from members where member_id = ?";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($key));
        

        //$res = $this->_db->fetchRow($sql);
/*
        $sqlW = "select a.contact_name as waris_name,
                w.email as waris_email,
                w.mobile_no as waris_mobile,
                w.phone as waris_phone,
                w.no_ktp as waris_ktp,
                w.member_contact_id as member_contact_idw
                from member_contact w, contact c
                where w.member_id = ? and w.contact_type_id = 2";

        $stmt2 = $this->_db->query($sqlW);
        $resW = $stmt2->execute(array($memberId));
 * 
 */

        //$resW = $this->_db->fetchRow($sqlW);

/*
        if (is_array($resW)){
            return array_merge($res, $resW);
        }else
 * 
 */
        return $res;
    }



    function getSponsorNetwork($sBy, $key, $parentCode) {
        
            $sql = "select m.*, ca.contact_mobile, concat(ca.contact_address,' ',ca.contact_city) as addr
                    from members m, member_contact mc, contact ca
                    where m.$sBy  = ? and member_type_id = 2 and
                    mc.member_id = m.member_id and mc.contact_type_id = 2
                    and ca.contact_id = mc.contact_id order by matrik1_level, m.member_id
                ";


            $stmt = $this->_db->query($sql);
            $res = $stmt->execute(array($key));    

            $this->tmpTree = "";

            $cnt = 0;



            $this->tmpTree .= "<ul>\n";

            //for($k = 0;$k<$num;$k++) {

            while($res->next()){
                $result = $res->current();
                $cnt++;
                $this->tmpTree .= "[SPONSOR1]";
                $sponsor[] = "<li title='<center>".strtoupper($result['member_name']."<br>".$result['mobile_no']."<br>".$result['addr'])."</center>' data-actor='".$result['member_id']."' class='small' ><br>".$result['member_code'];


                $sql2 = "select m.*, ca.contact_mobile, concat(ca.contact_address,' ',ca.contact_city) as addr
                        from members m, member_contact mc, contact ca
                        where m.$sBy  = ? and member_type_id = 2 and
                        mc.member_id = m.member_id and mc.contact_type_id = 2
                        and ca.contact_id = mc.contact_id order by matrik1_level, m.member_id
                        ";

                $stmt2 = $this->_db->query($sql2);
                $res2 = $stmt2->execute(array($result['sponsor_id']));

                $this->tmpTree .= "<ul>\n";

                while($res2->next()){
                    $result2 = $res2->current();
                    
                    $cnt++;
                    $this->tmpTree .= "[SPONSOR2]";
                    $sponsor[] = "<li title='<center>".strtoupper($result2['member_name']."<br>".$result2['mobile_no']."<br>".$result2['addr']."</center>' data-actor='".$result2['member_id'])."' class='small'><br>".$result2['member_code'];


                    $sql3 = "select m.*, ca.contact_mobile, concat(ca.contact_address,' ',ca.contact_city) as addr
                            from members m, member_contact mc, contact ca
                            where m.$sBy  = ? and member_type_id = 2 and
                            mc.member_id = m.member_id and mc.contact_type_id = 2
                            and ca.contact_id = mc.contact_id order by matrik1_level, m.member_id
                        ";

                    $stmt3 = $this->_db->query($sql3);
                    $res3 = $stmt3->execute(array($result2['sponsor_id']));                   

                   
                    $this->tmpTree .= "<ul>\n";

                    while($res3->next()){
                        $result3 = $res3->current();
                        $cnt++;
                        $this->tmpTree .= "[SPONSOR3]";
                        $sponsor[] = "<li title='<center>".strtoupper($result3['member_name']."<br>".$result3['mobile_no']."<br>".$result3['addr'])."</center>' data-actor='".$result3['member_id']."' class='small'><br>".$result3['member_code'];


                        $sql4 = "select m.*, ca.contact_mobile, concat(ca.contact_address,' ',ca.contact_city) as addr
                                from members m, member_contact mc, contact ca
                                where m.$sBy  = ? and member_type_id = 2 and
                                mc.member_id = m.member_id and mc.contact_type_id = 2
                                and ca.contact_id = mc.contact_id order by matrik1_level, m.member_id
                            ";

                        $stmt4 = $this->_db->query($sql4);
                        $res4 = $stmt4->execute(array($result3['sponsor_id']));

                        
                        $this->tmpTree .= "<ul>\n";

                        while($res4->next()){
                            $result4 = $res4->current();
                            $cnt++;
                            $this->tmpTree .= "[SPONSOR4]";
                            $sponsor[] = "<li title='<center>".strtoupper($result4['member_name']."<br>".$result4['mobile_no']."<br>".$result4['addr'])."</center>' data-actor='".$result4['member_id']."' class='small'><br>".$result4['member_code'];


                            $sql5 = "select m.*, ca.contact_mobile, concat(ca.contact_address,' ',ca.contact_city) as addr
                                    from members m, member_contact mc, contact ca
                                    where m.$sBy  = ? and member_type_id = 2 and
                                    mc.member_id = m.member_id and mc.contact_type_id = 2
                                    and ca.contact_id = mc.contact_id order by matrik1_level, m.member_id
                                ";

                            $stmt5 = $this->_db->query($sql5);
                            $res5 = $stmt5->execute(array($result4['sponsor_id']));                           

                         
                            $this->tmpTree .= "<ul>\n";

                            while($res5->next()){
                                $result5 = $res5->current();
                                $this->tmpTree .= "[SPONSOR5]";
                                $sponsor[] = "<li title='<center>".strtoupper($result5['member_name']."<br>".$result5['mobile_no']."<br>".$result5['addr'])."</center>' data-actor='".$result5['member_id']."' class='small'><br>".$result5['member_code'];
                            }

                            $this->tmpTree .= "</ul>\n";
                            
                        }

                        $this->tmpTree .= "</ul>\n";

                    }

                    $this->tmpTree .= "</ul>\n";
                    

                }

                $this->tmpTree .= "</ul>\n";
                    
            }

            $this->tmpTree .= "</ul>\n";

            if ($cnt > 0){
                krsort($sponsor);
                $z = 1;
                foreach ($sponsor as $key => $val) {
                    $this->tmpTree = str_replace('[SPONSOR'.$z.']', $val, $this->tmpTree);
                    $z++;
                }
            }

            $resTree = array("tree" => $this->tmpTree);

            return $resTree;

    }


    function getMemberNetwork($sBy, $key, $parentCode) {
            /*
            $sql = "SELECT  level, hi.*, mc.mobile_no, concat(ca.addr3,' ',ca.addr4) as addr
                    FROM (
                    SELECT  CPriorMatrik(member_id) AS member_id, @level AS level
                    FROM (
                    SELECT  @start_with := $key, @id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                    ) ho
                    JOIN    members hi
                    ON      hi.member_id = ho.member_id
                    JOIN member_contact mc
                    ON hi.member_id = mc.member_id
                    JOIN contact_address ca
                    ON mc.member_contact_id = ca.member_contact_id
                    WHERE mc.contact_type_id = 1 and level = 1";
             *
             */

            $sql = "SELECT  level, hi.*
                    FROM (
                    SELECT  CPriorMatrik(member_id) AS member_id, @level AS level
                    FROM (
                    SELECT  @start_with := ?, @id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                    ) ho
                    JOIN    members hi
                    ON      hi.member_id = ho.member_id
                    and level = 1";

            $stmt = $this->_db->query($sql);
            $result = $stmt->execute(array($key));

            return $result;
    }


    function getMemberTrinary($sBy, $key, $parentCode) {
          
            $sql = "SELECT  level, hi.*
                    FROM (
                    SELECT  CPriorTrinary(member_id) AS member_id, @level AS level
                    FROM (
                    SELECT  @start_with := ?, @id := @start_with,@level := 0
                    ) vars, members
                    WHERE   @id IS NOT NULL
                    ) ho
                    JOIN    members hi
                    ON      hi.member_id = ho.member_id
                    and level = 1";

            $stmt = $this->_db->query($sql);
            $result = $stmt->execute(array($key));

            return $result;
    }


    function getSponsor($memberId){
        
        $sql = "SELECT m.member_id as id, parent_id, sponsor_id, start_dtm, member_code, member_name, img_profile,
                concat(ma.contact_address, ' ', ma.contact_city, ' ', ma.contact_province) as address,
                contact_phone, contact_email, contact_mobile,
                CASE date_format(start_dtm, '%d%m%Y')
                  WHEN date_format(now(),'%d%m%Y') THEN 'Today'
                  WHEN date_format(DATE_ADD(now(),INTERVAL -1 DAY),'%d%m%Y') THEN 'Yesterday'
                ELSE date_format(start_dtm, '%d %b %Y') END
                as dateline,
                date_format(start_dtm, '%H:%i') timeline
                from members m, member_contact mc, contact ma
                where sponsor_id = $memberId and mc.member_id = m.member_id and ma.contact_id = mc.contact_id
                order by m.member_id desc, sponsor_id  ";
                    //limit $start, $limit";


        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        return $result;
    }


    function getSponsorx($memberId, $when){

        $sql = "select * from (SELECT m.member_id as id, m.sponsor_id as _parentId, member_code, member_name,
                concat(ma.contact_address, ' ', ma.contact_city, ' ', ma.contact_province) as address,
                'closed' as state, img_profile,
                CASE date_format(start_dtm, '%d%m%Y')
                  WHEN date_format(now(),'%d%m%Y') THEN 'Today'
                  WHEN date_format(DATE_ADD(now(),INTERVAL -1 DAY),'%d%m%Y') THEN 'Yesterday'
                ELSE date_format(start_dtm, '%d %b %Y') END
                as dateline, date_format(start_dtm, '%H:%i') timeline
                FROM (
                    SELECT member_id, parent_id, 0 as sponsor_id,
                      start_dtm, member_code, member_name, img_profile
                    from members where member_id = $memberId
                    union
                    SELECT hi.member_id, parent_id, hi.sponsor_id,
                        hi.start_dtm, hi.member_code, hi.member_name, hi.img_profile
                    FROM (
                        SELECT  CPrior(member_id) AS member_id, @level AS level
                        FROM (
                            SELECT  @start_with := $memberId,@id := @start_with,@level := 0
                            ) vars, members
                        WHERE   @id IS NOT NULL
                        ) ho
                    JOIN    members hi
                    ON      hi.member_id = ho.member_id) m, member_contact mc, contact ma
                    where  mc.member_id = m.member_id and ma.contact_id = mc.contact_id
                    order by id desc, sponsor_id desc) xx ";
                    //limit $start, $limit";

        if ($when != "") $sql .= " where dateline = '$when'";
        //else $sql .= " ";

        $stmt = $this->_db->query($sql);
        $result = $stmt->execute();

        return $result;
    }

}
